﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace data_upload_HCollage
{
    public partial class UploadStudentForm : Form
    {
        public UploadStudentForm()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (upStd_openFileDialog.ShowDialog() == DialogResult.OK) 
            {
                txtFileLocation.Text = upStd_openFileDialog.FileName;
            }
               
        }

        private void btnUploadStd_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from student", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                string student_id = "";
                string academic_session = "";
                string group_id = cmbGroup_id.Text;
                string student_name = "";
                string fathername = "";
                string mothername = "";
                string ssc_board = "";
                string ssc_year = "";
                string ssc_group = "";
                string ssc_reg_no = "";
                string ssc_reg_year = "";
                string ssc_centre = "";
                string ssc_roll = "";
                string ssc_gpa = "";
                string ssc_opt = "";
                string ssc_school = "";
                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();                  
                    student_name = row[2].ToString();
                    fathername = row[3].ToString();
                    mothername = row[4].ToString();


                    ssc_board= row[5].ToString();
                    ssc_year= row[6].ToString();
                    ssc_group= row[7].ToString();
                    ssc_reg_no= row[8].ToString();
                    ssc_reg_year= row[9].ToString();
                    ssc_centre= row[10].ToString();
                    ssc_roll= row[11].ToString();
                    ssc_gpa= row[12].ToString();
                    ssc_opt= row[13].ToString();
                    ssc_school = row[14].ToString();

                    String insert_sql = " insert into student( student_id,academic_session, group_id, student_name,fathername,mothername)"
                           + " values('" + student_id + "','" + academic_session + "','" + group_id + "','" + student_name
                           + "','" + fathername + "','" + mothername + "'"
                       + ")";
                        MySqlCommand Cmd_insert = new MySqlCommand(insert_sql, connMysql);
                        Cmd_insert.ExecuteNonQuery();

                        String insert_edu_sql = " insert into education( student_id,academic_session, ssc_board, ssc_year,ssc_group,ssc_reg_no,ssc_reg_year,ssc_centre,ssc_roll,ssc_gpa,ssc_opt,ssc_school)"
                               + " values('" + student_id + "','" + academic_session + "','" + ssc_board + "','" + ssc_year + "','" + ssc_group + "','" + ssc_reg_no + "','"
                               + ssc_reg_year + "','" + ssc_centre + "','" + ssc_roll + "','" + ssc_gpa 
                               + "','" + ssc_opt + "','" + ssc_school + "'"
                           + ")";
                        MySqlCommand Cmd_edu_insert = new MySqlCommand(insert_edu_sql, connMysql);
                        Cmd_edu_insert.ExecuteNonQuery();

                        String insert_std_exam_merit_sql = " insert into std_exam_merit( student_id,academic_session)"
                                   + " values('" + student_id + "','" + academic_session + "'"
                                   + ")";
                        MySqlCommand Cmd_std_exam_merit = new MySqlCommand(insert_std_exam_merit_sql, connMysql);
                        Cmd_std_exam_merit.ExecuteNonQuery();


                    i++;
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }

        private void UploadStudentForm_Load(object sender, EventArgs e)
        {
            cmbGroup_id.Text = "Please Select Group";
        }
    }
}
